﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.EntityClient;
using DAL.Models;
using DAL.Properties;
using System.Data;

namespace DAL.BAL
{
    public static class Business
    {
        #region Fields and Types

        public static DosvidEntities1 dosvidEntities = null;

        public static User currentUser = null;

        public enum ViewVariantName { All, Owner, Nothing }

        public enum MyEntities { Positions, CommodityGroups, Territories, Users, Roles, Employers, Vacancies, Candidates,
                                Candidate_VacancyHistory,Backup,BackupOptions,CalendarEvents};

        #endregion

        #region Methods

        public static void Init(EntityConnection _entityConnection)
        {
            if ((dosvidEntities == null) && (_entityConnection != null))
            {
                dosvidEntities = new DosvidEntities1(_entityConnection);

                dosvidEntities.CommandTimeout = 600;
            }

            
        }

        public static void Save()
        {
            if (dosvidEntities != null)
                dosvidEntities.SaveChanges();
        }

        public static void Cancel()
        {
            var changes = dosvidEntities.ObjectStateManager.GetObjectStateEntries
                (EntityState.Added | EntityState.Modified | EntityState.Deleted);

            foreach (var changedEntity in changes)
            {
                if (changedEntity.State == EntityState.Modified || changedEntity.State == EntityState.Deleted)
                    dosvidEntities.Refresh(System.Data.Objects.RefreshMode.StoreWins, changedEntity.Entity);

                else if (changedEntity.State == EntityState.Added)
                    dosvidEntities.Detach(changedEntity.Entity);
            }

        }

        public static void Refresh(System.Data.Objects.RefreshMode _refreshMode, MyEntities _myEntities)
        {
            if (dosvidEntities != null)
            {
                switch (_myEntities)
                {
                    case MyEntities.Positions :
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Positions);
                        break;
                    case MyEntities.CommodityGroups:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.CommodityGroups);
                        break;
                    case MyEntities.Territories:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Territories);
                        break;
                    case MyEntities.Roles:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Roles);
                        break;
                    case MyEntities.Users:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Users);
                        break;
                    case MyEntities.Employers:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Employers);
                        break;
                    case MyEntities.Vacancies:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Vacancies);
                        break;
                    case MyEntities.Candidates:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Candidates);
                        break;
                    case MyEntities.Backup:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.BackUpHistories);
                        break;
                    case MyEntities.BackupOptions:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.BackUpOptions);
                        break;
                    case MyEntities.Candidate_VacancyHistory:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.Candidat_VacancyHistory);
                        break;
                    case MyEntities.CalendarEvents:
                        dosvidEntities.Refresh(_refreshMode, dosvidEntities.CalendarEvents);
                        break;
                }
            }
        }

        public static void Refresh(System.Data.Objects.RefreshMode _refreshMode, object _myEntities)
        {
            if (dosvidEntities != null)
            {
                dosvidEntities.Refresh(_refreshMode, _myEntities);
            }
        }

        public static void AddObject(string _entitySetName, object _entity)
        {
            if (dosvidEntities != null)
                dosvidEntities.AddObject(_entitySetName, _entity);
            
        }

        public static void DeleteObject(object _entity)
        {
            if (dosvidEntities != null)
                dosvidEntities.DeleteObject(_entity);
        }

        public static int GetCurrentUserRole()
        {
            return currentUser.RoleID;
        }
      
        public static bool Login(string _userName, string _Password)
        {
        
            var user = from n in dosvidEntities.Users
                       where n.UserName == _userName && n.Password == _Password 
                       select n;

            dosvidEntities.Refresh(System.Data.Objects.RefreshMode.StoreWins, user);

            if (user.Count() == 0)
            {
                throw new Exception(Resources.Error_Login_IncorrectUserOrPassword);
            }
            if (user.First().Active == false)
            {
                throw new Exception(Resources.Error_Login_InActiveUser);
            }
            else
            {
                currentUser = user.First();

                return true;
            }
        }

        #endregion

        #region Hiden dictionary

        public static IEnumerable<Role> GetRoles()
        {
            return from n in dosvidEntities.Roles
                   select n;
        }

        public static IEnumerable<Age> GetAges()
        {
            return from n in dosvidEntities.Ages
                   orderby n.AgeName
                   select n;
        }

        public static IEnumerable<SalaryRanx> GetSalaryRanges()
        {
            return from n in dosvidEntities.SalaryRanges
                   orderby n.SalaryRange
                   select n;
        }

        public static IEnumerable<CompareImportant> GetCompareImportants()
        {
            return from n in dosvidEntities.CompareImportants
                   orderby n.CompareImportantName
                   select n;
        }

        public static IEnumerable<HasCar> GetHasCar()
        {
            return from n in dosvidEntities.HasCars
                   select n;
        }

        public static IEnumerable<SexVariant> GetSexVariants(bool useInVacancy)
        {
            return from n in dosvidEntities.SexVariants
                   where n.UseInVacancy == useInVacancy || n.UseInVacancy == false
                   select n;
        }

        public static IEnumerable<InterviewResult> GetInterviewResults()
        {
            return from n in dosvidEntities.InterviewResults
                   orderby n.InterviewResultName
                   select n;
        }

        public static IEnumerable<BackUpType> GetBackupTypes()
        {
            return from n in dosvidEntities.BackUpTypes
                   select n;
        }
        #endregion

        #region Users

        public static IEnumerable<User> GetUsers()
        {
            if (currentUser.Role.ViewVariant5.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in dosvidEntities.Users
                       orderby n.UserName
                       select n;
            }
            else
                return null;
        }

        public static IEnumerable<object> GetUsers(bool? active)
        {
            if (active == null)
            {
                return from n in dosvidEntities.Users
                       select n;
            }
            else
            {
                return from n in dosvidEntities.Users
                       where n.Active == active
                       select n;
            }
        }

        public static bool CanEditUsers()
        {
            return currentUser.Role.Users_Edit;
        }

        #endregion

        #region Positions

        public static IEnumerable<Position> GetPositions()
        {
            if (currentUser.Role.ViewVariant3.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in dosvidEntities.Positions
                       orderby n.PositionName
                       select n;
            }
            else
                return null;
        }

        public static bool CanEditPositions()
        {
            return currentUser.Role.Positions_Edit;
        }
        #endregion

        #region Commodity Groups
        public static IEnumerable<CommodityGroup> GetCommodityGroups()
        {
            if (currentUser.Role.ViewVariant1.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in dosvidEntities.CommodityGroups
                       orderby n.CommodityGroupName
                       select n;
            }
            else
                return null;
        }

        public static bool CanEditCommodityGroups()
        {
            return currentUser.Role.CommodityGroups_Edit;
        }
        #endregion

        #region Territories
        public static IEnumerable<Territory> GetTerritories()
        {
            if (currentUser.Role.ViewVariant4.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in dosvidEntities.Territories
                       orderby n.TerritoryName
                       select n;
            }
            else
                return null;
        }

        public static bool CanEditTerritories()
        {
            return currentUser.Role.Territories_Edit;
        }
        #endregion

        #region Employers

        public static IEnumerable<Employer> GetEmployers()
        {
            if (currentUser.Role.ViewVariant2.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in dosvidEntities.Employers
                       orderby n.EmployerName
                       select n;
            }
            else
                if (currentUser.Role.ViewVariant2.ViewVariantName == ViewVariantName.Owner.ToString())
                {
                    return from n in dosvidEntities.Employers
                           where n.User.Id == currentUser.Id
                           orderby n.EmployerName
                           select n;
                }
                else
                    return null;
        }

        public static IEnumerable<Employer> GetEmployersLookUP()
        {
            return from n in dosvidEntities.Employers
                   orderby n.EmployerName
                   select n;
        }        

        public static bool CanEditEmployers()
        {
            return currentUser.Role.Employers_Edit;
        }

        public static bool CanEditFieldManager()
        {
            return (currentUser.RoleID == 1);
        }

        public static bool CanInsertEmployers()
        {
            return ((currentUser.RoleID == 1) || (currentUser.RoleID == 3)); 
        }

        #endregion

        #region Vacancies

        public static IQueryable<Vacancy> GetVacancies()
        {
            if (currentUser.Role.ViewVariant6.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in dosvidEntities.Vacancies
                       .Include("Employer")
                       .Include("Employer.User")
                       .Include("Position")
                       .Include("Candidate")
                       .Include("Vacancy_Types")
                       .Include("Candidat_VacancyHistory")
                       .Include("Candidat_VacancyHistory.InterviewResult")
                       .Include("HasCar")
                       .Include("SalaryRanx")
                       .Include("Age")
                       .Include("Age1")
                       .Include("SexVariant")
                       select n;
            }
            else
                if (currentUser.Role.ViewVariant6.ViewVariantName == ViewVariantName.Owner.ToString())
                {
                    return from n in dosvidEntities.Vacancies
                           .Include("Employer")
                           .Include("Employer.User")
                           .Include("Position")
                           .Include("Candidate")
                           .Include("Vacancy_Types")
                           .Include("Candidat_VacancyHistory")
                           .Include("Candidat_VacancyHistory.InterviewResult")
                           .Include("HasCar")
                           .Include("SalaryRanx")
                           .Include("Age")
                           .Include("Age1")
                           .Include("SexVariant")
                           where n.Employer.User.Id == currentUser.Id
                           select n;
                }
                else
                    return null;

        }

        public static Vacancy GetVacancyById(int id)
        {
            return (from n in dosvidEntities.Vacancies
                      .Include("Employer")
                      .Include("Employer.User")
                      .Include("Position")
                      .Include("Candidate")
                      .Include("Candidate.Candidat_Phones")
                      .Include("Candidate1")
                      .Include("Candidate1.Candidat_Phones")
                      .Include("Vacancy_Types")
                      .Include("Candidat_VacancyHistory")
                      .Include("Candidat_VacancyHistory.InterviewResult")
                      .Include("Candidat_VacancyHistory.Candidate.Candidat_Phones")
                      .Include("HasCar")
                      .Include("SalaryRanx")
                      .Include("Age")
                      .Include("Age1")
                      .Include("SexVariant")
                      .Include("Vacancy_CommodityGroups")
                      .Include("Vacancy_Territories")
                      .Include("CompareImportant")
                      .Include("CompareImportant1")
                      .Include("CompareImportant2")
                      .Include("CompareImportant3")
                      .Include("CompareImportant4")
                   where n.Id == id
                   select n).FirstOrDefault();
        }

        public static IEnumerable<Vacancy> GetVacanciesForCandidate(Candidate candidate)
        {
            var vacancies = from n in dosvidEntities.sp_GetVacanciesForCandidate(candidate.Id)
                            select n;

            if (currentUser.Role.ViewVariant6.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in vacancies
                       select n;
            }
            else
                if (currentUser.Role.ViewVariant6.ViewVariantName == ViewVariantName.Owner.ToString())
                {
                    return from n in vacancies
                           where n.Employer.User.Id == currentUser.Id
                           select n;
                }
                else
                    return null;
        }

        public static bool CanEditVacancies()
        {
            return currentUser.Role.Vacancies_Edit;
        }

        public static IEnumerable<Vacancy_Types> GetVacancyTypes()
        {
            return from n in dosvidEntities.Vacancy_Types
                   orderby n.TypeName
                   select n;
        }

        #endregion

        #region Candidates

        public static IQueryable<Candidate> GetCandidates()
        {
            if (currentUser.Role.ViewVariant.ViewVariantName == ViewVariantName.All.ToString())
            {
                return from n in dosvidEntities.Candidates
                       .Include("Candidat_Phones")
                       .Include("SexVariant")
                       .Include("Candidate_Types")
                       .Include("User")
                       .Include("Candidat_RangeTerritories")
                       .Include("Territory")
                       .Include("Candidate_Positions")
                       .Include("Candidat_VacancyHistory")
                       orderby n.LastName
                       select n;
            }
            else
                if (currentUser.Role.ViewVariant.ViewVariantName == ViewVariantName.Owner.ToString())
                {
                    return from n in dosvidEntities.Candidates
                           .Include("Candidat_Phones")
                           .Include("SexVariant")
                           .Include("Candidate_Types")
                           .Include("User")
                           .Include("Candidat_RangeTerritories")
                           .Include("Territory")
                           .Include("Candidate_Positions")
                           .Include("Candidat_VacancyHistory")
                           where n.User.Id == currentUser.Id
                           orderby n.LastName
                           select n;
                }
                else
                    return null;
        }

        public static Candidate GetCandidateById(int id)
        {
            return (from n in dosvidEntities.Candidates
                      .Include("Candidat_Phones")
                      .Include("SexVariant")
                      .Include("Candidate_Types")
                      .Include("User")
                      .Include("Candidat_RangeTerritories")
                      .Include("Territory")
                      .Include("Candidate_Positions")
                      .Include("Candidat_EMails")
                      .Include("Candidat_VacancyHistory")
                      .Include("Candidat_EMails")
                      .Include("Candidat_ExceptCommodityGroups")
                      .Include("Candidat_ExceptEmployers")
                      .Include("Candidat_Expiriences")
                    where n.Id == id
                   select n).FirstOrDefault();
        }

        
        public static IEnumerable<Candidate> GetEmployees(Employer employer)
        {
            return from n in dosvidEntities.Candidates
                   from n1 in n.Employer_ContactPersons
                   where n1.EmployerId == employer.Id
                   orderby n.LastName
                   select n;
        }

        public static bool CanEditCandidates()
        {
            return currentUser.Role.Candidates_Edit;
        }

        public static bool CanInsertCandidatesAsContactPersons()
        {
            return currentUser.Role.Id == 3;
        }

        public static IEnumerable<Candidate> GetCandidatesForVacancy(Vacancy vacancy)
        {
            return from n in dosvidEntities.sp_GetCandidatesForVacancies(vacancy.Id)
                       select n;
        }

        public static bool CanFindCandidate()
        {
            return ((currentUser.RoleID == 1) || (currentUser.RoleID == 3));
        }

        public static bool CanSeeCandidateContacts()
        {
            return ((currentUser.RoleID == 1) || (currentUser.RoleID == 2) || (currentUser.RoleID == 3));
        }

        public static IEnumerable<Candidate_Types> GetCandidateTypes()
        {
            return from n in dosvidEntities.Candidate_Types
                   orderby n.TypeName
                   select n;
        }

        public static IEnumerable<Candidate> GetRealCandidatesLookup()
        {
            return from n in dosvidEntities.Candidates
                   where n.IsCandidate == true && n.CandidateTypeId == 4
                   orderby n.LastName
                   select n;
        }

        #endregion

        #region Backup

        public static string sp_Backup()
        {
            try
            {
                dosvidEntities.sp_Backup();

                return "OK";
            }
            catch (Exception e)
            {
                return e.InnerException.Message;
            }
        }

        public static string sp_BackupCustom(string filePath)
        {
            try
            {
                dosvidEntities.sp_BackupCustom(filePath);

                return "OK";
            }
            catch (Exception e)
            {
                throw new Exception (e.InnerException != null ? e.InnerException.Message : e.Message);
            }
        }

        public static IEnumerable<BackUpHistory> GetBackupHistory()
        {
            return from n in dosvidEntities.BackUpHistories
                   select n;
        }

        public static bool CanEditBackups()
        {
            return currentUser.RoleID == 1;
        }

        public static IEnumerable<BackUpOption> GetBackupOptions()
        {
            return from n in dosvidEntities.BackUpOptions
                   select n;
        }

        #endregion

        #region Calendar

        public static IEnumerable<CalendarEvent> GetCalendarEvents(DateTime EventDate)
        {
            return from n in dosvidEntities.CalendarEvents
                   where (n.AssignedUserId == currentUser.Id &&
                          n.EventDate == EventDate)
                   orderby n.EventTime
                   select n;
        }

        public static IEnumerable<CalendarEvent> GetOpenCalendarEventsFromPrevDays(DateTime EventDate)
        {
            return from n in dosvidEntities.CalendarEvents
                   where (n.AssignedUserId == currentUser.Id &&
                          n.EventDate < EventDate &&
                          (n.CalendarEventStatus.Id == 1 || n.CalendarEventStatus.Id == 2))
                   select n;
        }

        public static IEnumerable<CalendarEventStatus> GetCalendarEventStatuses()
        {
            return from n in dosvidEntities.CalendarEventStatuses
                   select n;
        }
        
        public static IEnumerable<CalendarEventType> GetCalendarEventTypes()
        {
            return from n in dosvidEntities.CalendarEventTypes
                   select n;
        }
        
        public static void ChangeEventTime(User user, DateTime date, List<CalendarEvent> Events)
        {
            var userEvents = (from n in dosvidEntities.CalendarEvents
                              where (n.AssignedUserId == user.Id &&
                                    n.EventDate == date)
                              select n);
            
            
            TimeSpan maxtime = TimeSpan.MinValue;
            
            foreach(CalendarEvent ce in userEvents)
            {
                maxtime = ce.EventTime > maxtime ? ce.EventTime : maxtime;
            }

            if (maxtime == TimeSpan.MinValue)
                maxtime = new TimeSpan(8, 55, 0);
          
            foreach (CalendarEvent ce in Events)
            {
                TimeSpan t = maxtime.Add(new TimeSpan(0,5,0));

                if (t <= new TimeSpan(19,0,0))
                    maxtime = maxtime.Add(new TimeSpan(0,5,0));

               ce.EventTime = maxtime;
            }
        }

        public static bool IsOpenedEvents()
        {
            var res = from n in dosvidEntities.CalendarEvents
                      where (n.AssignedUserId == currentUser.Id && n.EventDate <= DateTime.Today &&
                             (n.CalendarEventStatus.Id == 1 || n.CalendarEventStatus.Id == 2))
                      select n;

            return res.Count() != 0 ? true : false;
        }

        public static string CreateCallEventText(Vacancy vacancy, Candidate candidate)
        {
            string contactPersons = string.Empty;

            string phones = string.Empty;

            if (vacancy.Candidate != null)
            {
                foreach (Candidat_Phones cp in vacancy.Candidate.Candidat_Phones)
                {
                    if (phones == string.Empty)
                        phones += cp.Phone;
                    else
                        phones += string.Format(", {0}",  cp.Phone);
                }

                contactPersons += string.Format("{0} тел.({1})", vacancy.Candidate.LastName, phones);
            }

            phones = string.Empty;

            if (vacancy.Candidate1 != null)
            {
                foreach (Candidat_Phones cp in vacancy.Candidate1.Candidat_Phones)
                {
                    if (phones == string.Empty)
                        phones += cp.Phone;
                    else
                        phones += string.Format(", {0}" + cp.Phone);
                }

                if (contactPersons == string.Empty)
                    contactPersons += string.Format("{0} тел.({1})", vacancy.Candidate1.LastName, phones);
                else
                    contactPersons += string.Format("или {0} тел.({1})", vacancy.Candidate1.LastName, phones);

            }

            return string.Format("Перезвонить контактным лицам {0} роботодателя {1} насчет кандидата {2}", contactPersons, vacancy.Employer.EmployerName, candidate.LastName);
        }


        #endregion

        #region Reports

        public static List<ReportPOCO> GetCandidatesReportPerDay(DateTime dateFrom, DateTime dateTo, int candidateType)
        {
            IEnumerable<Candidate> candidates = null;

            if (currentUser.Role.ViewVariant.ViewVariantName == ViewVariantName.All.ToString())
            {
                candidates = from n in dosvidEntities.Candidates
                             orderby n.LastName
                             select n;
            }
            else
                if (currentUser.Role.ViewVariant.ViewVariantName == ViewVariantName.Owner.ToString())
                {
                    candidates = from n in dosvidEntities.Candidates
                                 where n.User.Id == currentUser.Id
                                 orderby n.LastName
                                 select n;
                }
                else
                {
                    candidates = from n in dosvidEntities.Candidates
                                 where n.User.Id == -1
                                 orderby n.LastName
                                 select n;
                }

            List<ReportPOCO> reportValues = new List<ReportPOCO>();

            var list = dosvidEntities.Candidat_TypeHistory
                         .Where(c => candidates.Any(c1 => c1.Id == c.CandidatID) && c.Date >= dateFrom && c.Date <= dateTo &&
                                c.IsCandidate == true && c.CandidateTypeId == candidateType)
                         .GroupBy(c => new { c.Date })
                         .Select(v => new
                         {
                             Date = v.Key.Date,
                             Count = v.Count()
                         })
                         .ToList();


            foreach (var item in list)
            {
                if (item.Count != 0)
                {
                    reportValues.Add(new ReportPOCO()
                    {
                        Value = item.Count,
                        Date = item.Date.ToShortDateString()
                    });
                }
            }

            return reportValues;
        }

        public static List<ReportPOCO> GetCandidatesReportComulative(DateTime dateFrom, DateTime dateTo, int candidateType)
        {
            List<ReportPOCO> reportValues = new List<ReportPOCO>();

            var list = dosvidEntities.DayStatistics
                .Where(ds => ds.Date >= dateFrom && ds.Date <= dateTo)
                 .Select(ds => new
                 {
                     Date = ds.Date,
                     Value = candidateType == 4 ? 
                                    ds.CandidatesActive.HasValue ? ds.CandidatesActive.Value : 0
                             : candidateType == 3 ? 
                                    ds.CandidateTrial.HasValue ? ds.CandidateTrial.Value : 0
                             : ds.CandidatesWork.HasValue ? ds.CandidatesWork.Value : 0
                 })
                 .ToList();

            foreach (var item in list)
            {
                if (item.Value != 0)
                {
                    reportValues.Add(new ReportPOCO()
                    {
                        Value = item.Value,
                        Date = item.Date.ToShortDateString()
                    });
                }
            }

            return reportValues;
        }
    
        public static List<ReportPOCO> GetVacanciesReportPerDay(DateTime dateFrom, DateTime dateTo)
        {
            IEnumerable<Vacancy> vacancies = null;

            if (currentUser.Role.ViewVariant6.ViewVariantName == ViewVariantName.All.ToString())
            {
                vacancies= from n in dosvidEntities.Vacancies
                       select n;
            }
            else
                if (currentUser.Role.ViewVariant6.ViewVariantName == ViewVariantName.Owner.ToString())
                {
                    vacancies = from n in dosvidEntities.Vacancies
                                where n.Employer.User.Id == currentUser.Id
                                select n;
                }
                else
                {
                    vacancies = from n in dosvidEntities.Vacancies
                                where n.Employer.User.Id == -1
                                select n;
                }

            List<ReportPOCO> reportValues = new List<ReportPOCO>();
            
            var list = dosvidEntities.Vacancies_TypeHistory
                          .Where(v => v.Date >= dateFrom && v.Date <= dateTo && vacancies.Any(v1 => v1.Id == v.VacancyId) &&
                                 v.VacancyTypeId == 4)
                          .GroupBy(v => v.Date)
                          .Select(v => new
                          {
                              Date = v.Key,
                              Count = v.Count()
                          })
                          .ToList();


            foreach (var item in list)
            {
                if (item.Count != 0)
                {
                    reportValues.Add(new ReportPOCO()
                    {
                        Value = item.Count,
                        Date = item.Date.ToShortDateString()
                    });
                }
            }

            return reportValues;
        }

        public static List<ReportPOCO> GetVacanciesReportComulative(DateTime dateFrom, DateTime dateTo)
        {
            List<ReportPOCO> reportValues = new List<ReportPOCO>();

            var list = dosvidEntities.DayStatistics
                .Where(ds => ds.Date >= dateFrom && ds.Date <= dateTo)
                 .Select(ds => new
                 {
                     Date = ds.Date,
                     Value = ds.Vacancies.HasValue ? ds.Vacancies.Value : 0
                 })
                 .ToList();

            foreach (var item in list)
            {
                if (item.Value != 0)
                {
                    reportValues.Add(new ReportPOCO()
                    {
                        Value = item.Value,
                        Date = item.Date.ToShortDateString()
                    });
                }
            }

            return reportValues;
        }

        public static List<ReportPOCO> GetEmployersReportComulative(DateTime dateFrom, DateTime dateTo, int reportType)
        {
            List<ReportPOCO> reportValues = new List<ReportPOCO>();

            var list = dosvidEntities.DayStatistics
                .Where(ds => ds.Date >= dateFrom && ds.Date <= dateTo)
                 .Select(ds => new
                 {
                     Date = ds.Date,
                     Value = reportType == 0 ? 
                                ds.EmployersPerson.HasValue ? ds.EmployersPerson.Value : 0
                             : ds.Employers.HasValue ? ds.Employers.Value : 0
                 })
                 .ToList();

            foreach (var item in list)
            {
                if (item.Value != 0)
                {
                    reportValues.Add(new ReportPOCO()
                    {
                        Value = item.Value,
                        Date = item.Date.ToShortDateString()
                    });
                }
            }

            return reportValues;
        }

        public static List<ReportPOCO> GetSendResumeReportPerDay(DateTime dateFrom, DateTime dateTo, int reportType)
        {
            List<ReportPOCO> reportValues = new List<ReportPOCO>();

            IEnumerable<Candidate> candidates = null;

            IEnumerable<Vacancy> vacancies = null;

            if (currentUser.RoleID == 1)
            {
                candidates = from n in dosvidEntities.Candidates
                             select n;

                vacancies = from n in dosvidEntities.Vacancies
                            select n;
            }
            else
                if (currentUser.RoleID == 2)
                {
                    candidates = from n in dosvidEntities.Candidates
                                 where n.User.Id == currentUser.Id
                                 select n;

                    vacancies = from n in dosvidEntities.Vacancies
                                where n.Id == -1
                                select n;
                }
                else
                {
                    candidates = from n in dosvidEntities.Candidates
                                 where n.Id == -1
                                 select n;

                    vacancies = from n in dosvidEntities.Vacancies
                                where n.Employer.User.Id == currentUser.Id
                                select n;
                }
              
            
            var list = dosvidEntities.SentEmailsHistories
                         .Where(c => (candidates.Any(c1 => c1.Id == c.CandidatId) ||
                                (vacancies.Any(c1 => c1.Id == c.VacancyId)))&&
                                c.SendDate >= dateFrom && c.SendDate <= dateTo &&
                                ((reportType == 0 && c.IsContactInfoAttached == false) ||
                                 (reportType == 1 && c.IsContactInfoAttached == true)))
                         .GroupBy(c => new { c.SendDate, c.CandidatId, c.VacancyId })
                         .Select(v => new
                         {
                             Date = v.Key.SendDate,
                             Count = v.Count()
                         })
                         .ToList();

            int days = dateTo.Subtract(dateFrom).Days;

            for (int i = 0; i <= days; i++)
            {
                DateTime tmpDate = dateFrom.AddDays(i);

                int count = 0;

                foreach (var item in list)
                {
                    if (item.Date == tmpDate)
                    {
                        count++;
                    }
                }

                if (count != 0)
                {
                    reportValues.Add(new ReportPOCO()
                    {
                        Value = count,
                        Date = tmpDate.ToShortDateString()
                    });
                }
            }
            
            return reportValues;
        }
 
        public static void RefreshDayStatistics()
        {
            dosvidEntities.sp_PrepareStatistic();
        }

        #endregion

    }
}
